﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Data;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.Core
{
    public delegate string CreateSqlWhereDelegate(JObject sqlWhereParamValues, ExpressSqlModel expressSqlModel, ref int index);

    public class ExpressSqlHelper
    {
        public static string CreateSqlWhere(ExpressSqlModel expressSqlModel, JObject sqlWhereParamValues, CreateSqlWhereDelegate createSqlWhereDelegate)
        {
            var sqlIndex = 1;
            if (expressSqlModel.SqlExpressType == ESqlExpressType.Condition)
            {
                return createSqlWhereDelegate(sqlWhereParamValues, expressSqlModel, ref sqlIndex);
            }
            else
            {
                return $"({CreateComplexSql(expressSqlModel, sqlWhereParamValues, ref sqlIndex, createSqlWhereDelegate)})";
            }
        }

        private static string CreateComplexSql(ExpressSqlModel expressSqlModel, JObject sqlWhereParamValues,ref int sqlIndex, CreateSqlWhereDelegate createSqlWhereDelegate)
        {
            string strResutl = "";
            string endCondition = "";
            if (expressSqlModel.SqlExpressType == ESqlExpressType.And)
            {
                endCondition = "AND";
            }
            else
            {
                endCondition = "OR";
            }
            int index = 1;
            foreach (var childExpress in expressSqlModel.Children)
            {
                string tempCondition = index == expressSqlModel.Children.Count ? "" : $" {endCondition} ";
                if (childExpress.SqlExpressType == ESqlExpressType.Condition)
                {
                    if(childExpress.Value != null)
                    {
                        strResutl += $"{createSqlWhereDelegate(sqlWhereParamValues, childExpress, ref sqlIndex)}{ tempCondition }";
                    }
                }
                else
                {
                    strResutl += $"({CreateComplexSql(childExpress, sqlWhereParamValues, ref sqlIndex, createSqlWhereDelegate)}){tempCondition}";
                }
                index++;
            }

            return strResutl;
        }

        public static string TestCreateConditionSql(JObject sqlWhereParamValues, ExpressSqlModel expressSqlModel, ref int index)
        {
            string preMark = "`";
            string postMark = "`";

            var conditionType = expressSqlModel.ConditionType;
            var field = expressSqlModel.Field;
            StringBuilder sbSqlWhere = new StringBuilder();
            switch (conditionType)
            {
                case EConditionType.等于:
                    sbSqlWhere.Append($"{preMark}{field}{postMark}=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Like:
                    sbSqlWhere.Append($"{preMark}{field}{postMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.In:
                    sbSqlWhere.Append($"{preMark}{field}{postMark} IN @SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Between:
                    sbSqlWhere.Append($"{preMark}{field}{postMark} BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
                    var inValues = expressSqlModel.Value as ArrayList;
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_1", inValues[0]));
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}_2", inValues[1]));
                    break;
                case EConditionType.大于:
                    sbSqlWhere.Append($"{preMark}{field}{postMark}>@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.大于等于:
                    sbSqlWhere.Append($"{preMark}{field}{postMark}>=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.小于:
                    sbSqlWhere.Append($"{preMark}{field}{postMark}<@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.小于等于:
                    sbSqlWhere.Append($"{preMark}{field}{postMark}<=@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.不等于:
                    sbSqlWhere.Append($"{preMark}{field}{postMark}<>@SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                case EConditionType.Null:
                    sbSqlWhere.Append($"{preMark}{field}{postMark} IS NULL");
                    break;
                case EConditionType.NotNull:
                    sbSqlWhere.Append($"{preMark}{field}{postMark} IS NOT NULL");
                    break;
                case EConditionType.NotIn:
                    sbSqlWhere.Append($"{preMark}{field}{postMark} NOT IN @SW{index}_{field}");
                    sqlWhereParamValues.Add(new JProperty($"SW{index}_{field}", expressSqlModel.Value));
                    break;
                default:
                    break;
            }

            index++;

            return sbSqlWhere.ToString();
        }

        public static JsonSerializer CreateCamelCaseJsonSerializer()
        {
            return new JsonSerializer { ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver() };
        }
    }
}
